# @Project: diablo
# @File: naitie_init
# @Time: 2024/5/16 19:26
# @User: Jolly
# @Email: jolly@007vin.com

"""
耐铁定价初始化
"""

import requests
import datetime
# 小汽配2.0初始化商品定价
# 定价来源：客户提供的excel
from copy import deepcopy

import time
import threading
from queue import Queue

from io import BytesIO
from openpyxl import load_workbook
from pymongo import MongoClient
from pymongo.errors import ConnectionFailure
import pymysql
from dbutils.pooled_db import PooledDB

lock = threading.Lock()


class InitAgreementPrice:

    def __init__(self, merchant_id, agreement_excel_id, file_path):

        self.merchant_id = merchant_id
        self.agreement_excel_id = agreement_excel_id
        self.file_path = f"./{file_path}.xlsx"

        start_time = time.time()

        # 初始化数据库连接
        self.mysql_conn = self.get_mysql_connection()

        # 初始化数据库连接
        # 2.0生产库
        self.diablo_conn_2_0 = self.get_mongo_connection("root", "aa123456", "192.168.191.175", "27017")

        # 执行任务
        self.task()

        print("========= 数据插入,共耗时:{}'s =========".format(round(time.time() - start_time, 3)))

    def get_mongo_connection(self, username, password, host, port):
        try:
            # 创建 MongoClient
            client = MongoClient(f"mongodb://{username}:{password}@{host}:{port}",
                                 maxPoolSize=80,
                                 minPoolSize=1,
                                 maxIdleTimeMS=30000,
                                 waitQueueTimeoutMS=2000)

            # 检测连接是否可用，如果连接不可用则抛出异常
            if not client:
                raise ConnectionFailure("从MongoDB连接池中获取连接失败！")

            return client

        except ConnectionFailure as e:
            # 处理连接失败的异常
            print(f"连接MongoDB失败: {e}")
            return None

    def get_mysql_connection(self):
        maxconnections = 40  # 最大连接数
        pool = PooledDB(
            pymysql,
            maxconnections,
            host='192.168.191.21',
            user='users',
            port=3306,
            passwd='uSR@ll7GnJDopei',
            db='users',
            use_unicode=True)

        # pool = PooledDB(
        #     pymysql,
        #     maxconnections,
        #     host='192.168.191.47',
        #     user='users',
        #     port=3306,
        #     passwd='uSR@#H^&JD',
        #     db='users',
        #     use_unicode=True)

        return pool

    def get_code(self, oe, quality):
        """
        获取指定品质商品编码
        """
        code = ""

        mysql_conn = self.mysql_conn.connection()

        try:
            cursor = mysql_conn.cursor()

            sql = f"""select id, quality from diablo_quality_num where status=1 and quality='{quality}' """

            cursor.execute(sql)

            result = cursor.fetchall()

            if len(result) > 0:
                for result_ in result:
                    # print(result_)
                    return 'NTW-' + oe + '.' + str(result_[0])

        except Exception as e:
            print("商品编码信息失败 - mysql：", e)
        finally:
            mysql_conn.close()

        return code

    # 写入数据
    def write_data(self, *args):

        oe = args[0]
        quality = args[1]
        stock_num = args[2]
        stock_price = args[3]
        price = args[4]

        # code去除空格
        oe = oe.strip()
        oe = oe.replace(' ', '')

        # 根据oe获取商品编码
        code = self.get_code(oe, quality)
        if code == "":
            return

        # 库存价和库存数量写入到goods表里
        self.insert_stock_price(code, stock_num, stock_price)

        # 处理数字小数点
        if '.' in str(price):
            integer_part, decimal_part = str(price).split('.')
            decimal_part = decimal_part[:2]
            price = float(f'{integer_part}.{decimal_part}')
        else:
            price = float(price)

        filter_dict_exist = {
            "code": code,
            "merchant_id": self.merchant_id,
            "agreement_excel_id": self.agreement_excel_id
        }

        update_dict = {
            "agreement_excel_id": self.agreement_excel_id,
            "merchant_id": self.merchant_id,
            "code": code,
            "agreement_price": float(price),
            "price_type": "customize",
            "price_value": float(price),
            "price_desc": f"¥ = {str(price)}",
            "update_time": datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
            "user_id": "0000000000000000003",  # 当前用户id
            "price_user": "系统定价",  # 当前用户名
            "price_time": datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        }

        # 获取2.0MongoDB连接
        connection = self.diablo_conn_2_0

        try:
            if connection is not None:
                # 执行数据库操作
                db = connection["inventory_data"]
                conn = db["agreement_goods_price"]
                conn.update(filter_dict_exist, update_dict, upsert=True)

        except Exception as e:
            print("写入2.0MongoDB数据库单据失败：", e)
        finally:
            # 将连接放回连接池
            connection.close()

    # 获取excel数据，生成器返回
    def get_excel_data(self):

        with open(self.file_path, "rb") as file:
            content = file.read()

        # 将内容转换为BytesIO对象
        bytesIO = BytesIO(content)

        # 加载工作簿
        book = load_workbook(bytesIO, read_only=True)

        # 获取活动工作表
        sheet = book.active

        previous = next(sheet.iter_rows(), None)
        for row in sheet.iter_rows():
            yield previous, False
            previous = row
        yield previous, True

    def get_all_excel_data(self):
        """
        获取全部excel
        """

        with open(self.file_path, "rb") as file:
            content = file.read()

        # 将内容转换为BytesIO对象
        bytesIO = BytesIO(content)

        # 加载工作簿
        book = load_workbook(bytesIO, read_only=True)

        # 获取活动工作表
        sheet = book.active

        return sheet.iter_rows()

    def insert_stock_price(self, code, stock_num, stock_price):
        """
        写入库存价格和库存数量到goods表中
        """
        connection = self.diablo_conn_2_0

        try:
            if connection is not None:

                # 执行数据库操作
                db = connection["ddll"]
                conn = db["goods"]

                filter_dict = {
                    "merchant_id": self.merchant_id,
                    "code": code
                }

                update_dict = {
                    "$set": {
                        "stock_num" : int(stock_num),
                        "stock_price_type" : 1,
                        "stock_price" : float(stock_price),
                    }
                }
                conn.update(filter_dict, update_dict)

        except Exception as e:
            print("写入商品数据失败 - mongo：", e)
        finally:
            # 将连接放回连接池
            connection.close()

    # 任务执行
    def task(self):
        q = Queue(maxsize=40)  # 设定最大队列数和线程数

        count = 1

        all_data = self.get_all_excel_data()

        for row in all_data:
            oe = str(row[0].value)
            quality = row[1].value
            stock_num = row[2].value
            stock_price = row[3].value
            price = row[4].value

            t = threading.Thread(target=self.write_data, args=(oe, quality, stock_num, stock_price, price))
            q.put(t)
            if q.full() == True:
                thread_list = []
                while q.empty() == False:
                    t = q.get()
                    thread_list.append(t)
                    t.start()
                for t in thread_list:
                    t.join()

            print("当前写入数：", count, " oe为：", oe, "价格为：", price)

            count += 1

        # 处理残余队列数据
        thread_list = []
        while q.empty() == False:
            t = q.get()
            thread_list.append(t)
            t.start()
        for t in thread_list:
            t.join()


if __name__ == "__main__":

    InitAgreementPrice("4347f10530804bb09d9d7b68d6d07933", 495, "naitie")
